Release 10.1A: OpenEdge Data Management:
SQL Development

Understanding optimization

The OpenEdge SQL Engine contains a query optimizer that analyzes SQL queries and produces a plan for how SQL should best execute the query. The plan contains information such as which tables to access, in what order, and with which indexes. To produce a good query plan, the optimizer analyzes the query and considers many methods for each query execution step.

For instance, a table of customer orders might have eight different indexes: for accessing orders, order number, customer number, order date, delivery date, suppliers, plant number, sales person, and by combinations of those attributes. For example:

SELECT a,b,c, FROM pub.Orders WHERE CustNum = 1234 AND OrderDate = '01-04-2003' AND Supplier = 'Whittle Widgets';

Two candidate indexes might be XCust_Num and XSupplier. To choose one of these indexes, the optimizer estimates the cost to access data using that specific index. The optimizer measures cost in terms of time. The optimizer then chooses the least costly index. Index XCust_Num, for instance, might have an estimated cost of 25 milliseconds for the predicate CustNum = 1234, and the index XSupplier might have an estimated cost of 35 milliseconds for predicate
Supplier = ’Whittle Widgets’.

Clearly, then, estimating costs as accurately as possible is crucial to choosing the best index, and for all other choices the optimizer makes. For database tables, the optimizer’s cost estimates are based on how the table is accessed, and on the number of rows it expects to access. To estimate the number of rows, the optimizer uses statistics which the database owner has created using the SQL UPDATE STATISTICS command. It also uses rules about the type of index considered, such as a unique index, and about the type of predicate (such as '=' or BETWEEN) used to access an index.